package com.bckj.fastboot.core.sql;

import com.baomidou.mybatisplus.annotation.FieldStrategy;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import com.bckj.fastboot.core.lang.func.IGetter;
import com.bckj.fastboot.core.sql.meta.*;
import com.bckj.fastboot.core.util.LambdaMeta;
import com.bckj.fastboot.core.util.LambdaUtils;
import com.bckj.fastboot.core.util.SpringUtils;
import lombok.Getter;
import lombok.Setter;
import org.apache.ibatis.jdbc.SQL;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Type;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class StatementBuilder implements ConditionBuilder<StatementBuilder> {

    private static final Logger log = LoggerFactory.getLogger(StatementBuilder.class);

    private final static String ALIAS_REGEX = "_.";

    @Setter
    private FieldStrategy whereStrategy;

    // 以下为属性皆可重置

    /**
     * 别名映射 全类名=>别名
     */
    private Map<String, String> aliasMap;

    /**
     * 别名递增
     */
    private AtomicInteger aliasIncr;

    private SQL sql;

    private String cteSql;

    private String preSql;

    private String lastSql;

    private AtomicInteger paramNameSeq;

    @Getter
    private Map<String, Object> paramNameValuePairs;

    public StatementBuilder() {
        this(new SQL());
    }

    public StatementBuilder(SQL sql) {
        this.sql = sql;
        init();
    }

    public StatementBuilder(Type type, String alias, Object ... typeAliases) {
        this(new SQL());
        alias(type, alias, typeAliases);
    }

    public StatementBuilder(Database database) {
        this(new SQL());
        database.getTables().forEach(t -> alias(t.getEntityClass(), t.getAlias()));
    }

    private void init() {
        try {
            MybatisPlusProperties mybatisPlusProperties = SpringUtils.getBean(MybatisPlusProperties.class);
            whereStrategy = mybatisPlusProperties.getGlobalConfig().getDbConfig().getWhereStrategy();
        } catch (Exception e) {
            log.error(e.getMessage());
            whereStrategy = FieldStrategy.ALWAYS;
        }

        // 必要属性初始化
        aliasMap = new HashMap<>(10);
        aliasIncr = new AtomicInteger(0);
        paramNameSeq = new AtomicInteger(0);
        paramNameValuePairs = new HashMap<>(16);
    }

    /**
     * 重置必要属性
     */
    public void reset() {
        sql = new SQL();
        aliasMap.clear();
        aliasIncr.set(0);
        paramNameSeq.set(0);
        paramNameValuePairs.clear();
        cteSql = null;
        preSql = null;
        lastSql = null;
    }

    @Override
    public StatementBuilder getChildren() {
        return this;
    }

    public static StatementBuilder create() {
        return new StatementBuilder();
    }

    public static StatementBuilder create(Type type, String alias, Object ... typeAliases) {
        return new StatementBuilder(type, alias, typeAliases);
    }

    public StatementBuilder alias(Type type, String alias, Object ... typeAliases) {
        aliasMap.put(getTypeName(type), alias);
        if (typeAliases != null && typeAliases.length > 0) {
            Assert.isTrue(typeAliases.length % 2 == 0, "Type and alias length exception");
            for (int i = 0; i < typeAliases.length; i = i + 2) {
                String tn = getTypeName(typeAliases[i]);
                String s = (String) typeAliases[i + 1];
                aliasMap.put(tn, s);
            }
        }
        return this;
    }

    public StatementBuilder with(String name, String colName, String subQuery) {
        cteSql = String.format("WITH %s (%s) AS (%s)", name, colName, subQuery);
        return this;
    }

    /* select */

    @SafeVarargs
    public final <T> StatementBuilder select(IGetter<T>... getters) {
        return makeGetters(getters,
                p -> select(getColByLambda(p)),
                p -> select(Arrays.stream(p).map(this::getColByLambda).collect(Collectors.joining(","))));
    }

    public <T> StatementBuilder selectAs(IGetter<T> getter, String alias) {
        Assert.notNull(alias, "Alias cannot be empty");
        return selectAs(getColByLambda(getter), alias);
    }

    public StatementBuilder selectAs(String column, String alias) {
        sql.SELECT(column + " AS " + alias);
        return this;
    }

    public StatementBuilder select(String select) {
        sql.SELECT(select);
        return this;
    }

    public StatementBuilder select(TableLike table) {
        return select(table.getColumns());
    }

    public StatementBuilder select(Collection<? extends Column> columns) {
        sql.SELECT(columns.stream().map(Column::toString).collect(Collectors.joining(",")));
        return this;
    }

    public StatementBuilder selectAll(Class<?> clazz, Predicate<String> selectPredicate, Function<String, String> selectMapping) {
        String alias = getAndGenAlias(clazz);
        TableInfo tableInfo = TableInfoHelper.getTableInfo(clazz);
        return selectAll(tableInfo, alias, selectPredicate, selectMapping);
    }

    public StatementBuilder selectAll(TableInfo tableInfo, String alias, Predicate<String> selectPredicate, Function<String, String> selectMapping) {
        if (selectPredicate == null) {
            selectPredicate = p -> true;
        }
        if (selectMapping == null) {
            selectMapping = p -> p;
        }
        var keyStream = Stream.of(tableInfo.getKeyColumn());
        var fieldStream = tableInfo.getFieldList().stream().map(TableFieldInfo::getSqlSelect);
        String str =
                Stream.concat(keyStream, fieldStream)
                        .filter(selectPredicate)
                        .map(selectMapping)
                        .filter(Objects::nonNull)
                        .map(p -> alias + "." + p.trim())
                        .collect(Collectors.joining(","));
        sql.SELECT(str);
        return this;
    }
    
    public StatementBuilder select(Class<?> clazz, String select) {
        String alias = getAndGenAlias(clazz);
        Assert.notNull(alias, "Alias cannot be empty");
        sql.SELECT(doSelectTableAsAs(alias, select));
        return this;
    }

    public StatementBuilder select(TableLike table, String select) {
        sql.SELECT(doSelectTableAsAs(table.getAlias(), select));
        return this;
    }

    private String doSelectTableAsAs(String tableAs, String select) {
        if (select.contains(",")) {
            StringBuilder r = new StringBuilder();
            String[] fields = select.split(",", -1); // include full separator
            for (String field : fields) {
                r.append(tableAs).append(".").append(field.trim()).append(",");
            }
            return r.substring(0, r.length() - 1);
        } else {
            return tableAs + "." + select.trim();
        }
    }

    public StatementBuilder from(Class<?> clazz) {
        String alias = getAndGenAlias(clazz);
        return from(String.format("%s AS %s", getTableByType(clazz), alias));
    }

    public StatementBuilder from(String table) {
        sql.FROM(table);
        return this;
    }

    public StatementBuilder from(TableLike table) {
        sql.FROM(table.getTableName() + " AS " + table.getAlias());
        return this;
    }
    public <L, R> StatementBuilder join(Class<?> clazz, IGetter<L> l, IGetter<R> r) {
        return join(clazz, l, r, null);
    }

    public <L, R> StatementBuilder join(Class<?> clazz, IGetter<L> l, IGetter<R> r, String otherWhere) {
        return join(buildJoin(clazz, l, r, otherWhere));
    }

    public StatementBuilder join(String join) {
        sql.JOIN(join);
        return this;
    }

    public StatementBuilder join(TableLike table, Column l, Column r, String otherWhere) {
        return join(buildJoin(table, l, r, otherWhere));
    }

    public StatementBuilder join(TableLike table, Column l, Column r) {
        return join(buildJoin(table, l, r, null));
    }

    public <L, R> StatementBuilder innerJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r) {
        return innerJoin(clazz, l, r, null);
    }

    public <L, R> StatementBuilder innerJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r, String otherWhere) {
        return innerJoin(buildJoin(clazz, l, r, otherWhere));
    }

    public StatementBuilder innerJoin(String join) {
        sql.INNER_JOIN(join);
        return this;
    }

    public StatementBuilder innerJoin(TableLike table, Column l, Column r, String otherWhere) {
        return innerJoin(buildJoin(table, l, r, otherWhere));
    }

    public StatementBuilder innerJoin(TableLike table, Column l, Column r) {
        return innerJoin(buildJoin(table, l, r, null));
    }

    public <L, R> StatementBuilder leftJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r) {
        return leftJoin(clazz, l, r, null);
    }

    public <L, R> StatementBuilder leftJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r, String otherWhere) {
        return leftJoin(buildJoin(clazz, l, r, otherWhere));
    }

    public StatementBuilder leftJoin(String join) {
        sql.LEFT_OUTER_JOIN(join);
        return this;
    }

    public StatementBuilder leftJoin(TableLike table, Column l, Column r, String otherWhere) {
        return leftJoin(buildJoin(table, l, r, otherWhere));
    }

    public StatementBuilder leftJoin(TableLike table, Column l, Column r) {
        return leftJoin(buildJoin(table, l, r, null));
    }

    public <L, R> StatementBuilder rightJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r) {
        return rightJoin(clazz, l, r, null);
    }

    public <L, R> StatementBuilder rightJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r, String otherWhere) {
        return rightJoin(buildJoin(clazz, l, r, otherWhere));
    }

    public StatementBuilder rightJoin(String join) {
        sql.LEFT_OUTER_JOIN(join);
        return this;
    }

    public StatementBuilder rightJoin(TableLike table, Column l, Column r, String otherWhere) {
        return rightJoin(buildJoin(table, l, r, otherWhere));
    }

    public StatementBuilder rightJoin(TableLike table, Column l, Column r) {
        return rightJoin(buildJoin(table, l, r, null));
    }

    public <L, R> StatementBuilder outerJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r) {
        return outerJoin(clazz, l, r, null);
    }

    public <L, R> StatementBuilder outerJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r, String otherWhere) {
        return outerJoin(buildJoin(clazz, l, r, otherWhere));
    }

    public StatementBuilder outerJoin(String join) {
        sql.OUTER_JOIN(join);
        return this;
    }

    public StatementBuilder outerJoin(TableLike table, Column l, Column r, String otherWhere) {
        return outerJoin(buildJoin(table, l, r, otherWhere));
    }

    public StatementBuilder outerJoin(TableLike table, Column l, Column r) {
        return outerJoin(buildJoin(table, l, r, null));
    }

    private <L, R> String buildJoin(Class<?> clazz, IGetter<L> l, IGetter<R> r, String otherWhere) {
        String alias = getAndGenAlias(clazz);
        String join = String.format("%s AS %s ON %s = %s", getTableByType(clazz), alias, getColByLambda(l), getColByLambda(r));
        if (otherWhere != null) {
            join += otherWhere;
        }
        return join;
    }

    private String buildJoin(TableLike table, Column l, Column r, String otherWhere) {
        String alias = table.getAlias();
        String join = String.format("%s AS %s ON %s = %s", table.getTableName(), alias, l.getTableColumnName(), r.getTableColumnName());
        if (otherWhere != null) {
            join += " " + otherWhere;
        }
        return join;
    }

    public StatementBuilder where(Object entity) {
        Class<?> entityCls = entity.getClass();
        TableInfo tableInfo = TableInfoHelper.getTableInfo(entityCls);
        Assert.notNull(tableInfo, "Object is not an entity");
        String alias = aliasMap.get(entityCls.getName());
        for (TableFieldInfo fieldInfo : tableInfo.getFieldList()) {
            Object propertyValue = tableInfo.getPropertyValue(entity, fieldInfo.getProperty());
            if (checkValue(propertyValue)) {
                where(alias + "." + fieldInfo.getColumn() + " = " + formatValue(propertyValue));
            }
        }
        return this;
    }

    public StatementBuilder whereObj(Object obj) {
        QueryConditionBuilder.buildQueryCondition(sql, obj);
        return this;
    }

    public StatementBuilder whereObj(Object obj, String fieldPrefix) {
        QueryConditionBuilder.buildQueryCondition(sql, obj, fieldPrefix);
        return this;
    }

    public <T> StatementBuilder where(T query, Function<T, Object[]> func) {
        Object[] entities = func.apply(query);
        Assert.notEmpty(entities, "Entity list cannot be empty");
        for (Object entity : entities) {
            where(entity);
        }
        return this;
    }

    public StatementBuilder where(String whereSql) {
        sql.WHERE(whereSql);
        return this;
    }

    public <T> StatementBuilder where(IGetter<T> getter, Object value, String opt) {
        return where(getColByLambda(getter), opt, formatValue(value));
    }

    @Override
    public StatementBuilder where(String field, Object value, String opt) {
        if (checkValue(value)) {
            sql.WHERE(String.format("%s %s %s", field, opt, formatValue(value)));
        }
        return this;
    }

    @Override
    public StatementBuilder eq(String field, Object value) {
        return where(field, value, " = ");
    }

    @Override
    public StatementBuilder ne(String field, Object value) {
        return where(field, value, " != ");
    }

    @Override
    public StatementBuilder gt(String field, Object value) {
        return where(field, value, " > ");
    }

    @Override
    public StatementBuilder ge(String field, Object value) {
        return where(field, value, " >= ");
    }

    @Override
    public StatementBuilder lt(String field, Object value) {
        return where(field, value, " < ");
    }

    @Override
    public StatementBuilder le(String field, Object value) {
        return where(field, value, " <= ");
    }

    @Override
    public StatementBuilder like(String field, Object value) {
        if (checkValue(value)) {
            sql.WHERE(field + " LIKE CONCAT('%'," + formatValue(value) + ",'%')");
        }
        return this;
    }

    @Override
    public StatementBuilder likeLeft(String field, Object value) {
        if (checkValue(value)) {
            sql.WHERE(field + " LIKE CONCAT('%'," + formatValue(value) + ")");
        }
        return this;
    }

    @Override
    public StatementBuilder likeRight(String field, Object value) {
        if (checkValue(value)) {
            sql.WHERE(field + " LIKE CONCAT(" + formatValue(value) + ",'%')");
        }
        return this;
    }

    @Override
    public StatementBuilder between(String field, Object val1, Object val2) {
        if (checkValue(val1) && checkValue(val2)) {
            sql.WHERE(String.format("%s BETWEEN %s AND %s", field, formatValue(val1), formatValue(val2)));
        }
        return this;
    }

    @Override
    public StatementBuilder in(String field, Collection<?> coll) {
        if (CollectionUtils.isNotEmpty(coll)) {
            sql.WHERE(String.format("%s IN (%s)", field, coll.stream().map(this::formatValue).collect(Collectors.joining(","))));
        }
        return this;
    }

    @Override
    public StatementBuilder notIn(String field, Collection<?> coll) {
        if (CollectionUtils.isNotEmpty(coll)) {
            sql.WHERE(String.format("%s NOT IN (%s)", field, coll.stream().map(this::formatValue).collect(Collectors.joining(","))));
        }
        return this;
    }

    @SafeVarargs
    public final <T> StatementBuilder isNull(boolean condition, IGetter<T>... getters) {
        if (condition) {
            return isNull(getters);
        }
        return this;
    }

    @SafeVarargs
    @Override
    public final <T> StatementBuilder isNull(IGetter<T>... getters) {
        return makeGetters(getters,
                p -> isNull(getColByLambda(p)),
                p -> Arrays.stream(p).forEach(i -> isNull(getColByLambda(i))));
    }

    @Override
    public StatementBuilder isNull(String column) {
        sql.WHERE(column + " IS NULL");
        return this;
    }

    @SafeVarargs
    public final <T> StatementBuilder isNotNull(boolean condition, IGetter<T>... getters) {
        if (condition) {
            return isNotNull(getters);
        }
        return this;
    }

    @SafeVarargs
    @Override
    public final <T> StatementBuilder isNotNull(IGetter<T>... getters) {
        return makeGetters(getters,
                p -> isNotNull(getColByLambda(p)),
                p -> Arrays.stream(p).forEach(i -> isNotNull(getColByLambda(i))));
    }

    @Override
    public StatementBuilder isNotNull(String column) {
        sql.WHERE(column + " IS NOT NULL");
        return this;
    }

    public StatementBuilder and(boolean condition, Consumer<ConditionBuilder<?>> consumer) {
        if (condition) {
            and(consumer);
        }
        return this;
    }

    @Override
    public StatementBuilder and(Consumer<ConditionBuilder<?>> consumer) {
        ConditionBuilder<NestedConditionBuilder> builder = new NestedConditionBuilder();
        consumer.accept(builder);
        sql.WHERE(builder.toString());
        return this;
    }


    @SafeVarargs
    public final <T> StatementBuilder groupBy(boolean condition, IGetter<T>... getters) {
        if (condition) {
            return groupBy(getters);
        }
        return this;
    }

    @SafeVarargs
    public final <T> StatementBuilder groupBy(IGetter<T>... getters) {
        return makeGetters(getters,
                p -> groupBy(getColByLambda(p)),
                p -> groupBy(Arrays.stream(p).map(this::getColByLambda).collect(Collectors.joining(","))));
    }

    public <T> StatementBuilder groupBy(boolean condition, String columns) {
        if (condition) {
            return groupBy(columns);
        }
        return this;
    }

    public StatementBuilder groupBy(String columns) {
        sql.GROUP_BY(columns);
        return this;
    }

    public StatementBuilder groupBy(Column... columns) {
        if (columns == null || columns.length == 0) {
            throw new IllegalArgumentException("columns is empty");
        }
        if (columns.length == 1) {
            sql.GROUP_BY(columns[0].getTableColumnName());
        } else {
            sql.GROUP_BY(Arrays.stream(columns).map(Column::getTableColumnName).collect(Collectors.joining(",")));
        }
        return this;
    }

    @SafeVarargs
    public final <T> StatementBuilder orderByAsc(boolean condition, IGetter<T>... getters) {
        if (condition) {
            return orderByAsc(getters);
        }
        return this;
    }

    @SafeVarargs
    public final <T> StatementBuilder orderByAsc(IGetter<T>... getters) {
        return orderBy(true, getters);
    }

    @SafeVarargs
    public final <T> StatementBuilder orderByDesc(boolean condition, IGetter<T>... getters) {
        if (condition) {
            return orderByDesc(getters);
        }
        return this;
    }

    @SafeVarargs
    public final <T> StatementBuilder orderByDesc(IGetter<T>... getters) {
        return orderBy(false, getters);
    }

    @SafeVarargs
    public final <T> StatementBuilder orderBy(boolean isAsc, IGetter<T>... getters) {
        final String suffix = isAsc ? "" : " DESC";
        return makeGetters(getters,
                p -> orderBy(getColByLambda(p) + suffix),
                p -> orderBy(Arrays.stream(p)
                        .map(i -> getColByLambda(i) + suffix)
                        .collect(Collectors.joining(","))));
    }

    public StatementBuilder orderBy(boolean condition, String columns) {
        if (condition) {
            return orderBy(columns);
        }
        return this;
    }

    public StatementBuilder orderBy(String columns) {
        sql.ORDER_BY(columns);
        return this;
    }

    public StatementBuilder orderBy(boolean isAsc, Column... columns) {
        if (columns == null || columns.length == 0) {
            throw new IllegalArgumentException("columns is empty");
        }
        if (columns.length == 1) {
            sql.ORDER_BY(columns[0].getTableColumnName() + (isAsc ? "" : " DESC"));
        } else {
            sql.ORDER_BY(Arrays.stream(columns).map(i -> i.getTableColumnName() + (isAsc ? "" : " DESC")).collect(Collectors.joining(",")));
        }
        return this;
    }

    /* insert */

    public StatementBuilder insert(Class<?> clazz) {
        sql.INSERT_INTO(getTableByType(clazz));
        return this;
    }

    public StatementBuilder insert(String table) {
        sql.INSERT_INTO(table);
        return this;
    }

    public StatementBuilder values(String columns, String values) {
        sql.VALUES(columns, values);
        return this;
    }

    public <T> StatementBuilder values(IGetter<T> getter, Object value) {
        sql.VALUES(getColByLambda(getter, false), formatValue(value));
        return this;
    }

    /* update */

    public StatementBuilder update(Class<?> clazz) {
        sql.UPDATE(getTableByType(clazz));
        return this;
    }

    public StatementBuilder update(String table) {
        sql.UPDATE(table);
        return this;
    }

    public <T> StatementBuilder set(IGetter<T> getter, Object value) {
        if (checkValue(value)) {
            sql.SET(String.format("%s = %s", getColByLambda(getter, false), formatValue(value)));
        }
        return this;
    }

    public StatementBuilder set(String set) {
        sql.SET(set);
        return this;
    }

    /* delete */

    public StatementBuilder delete(Class<?> clazz) {
        sql.DELETE_FROM(getTableByType(clazz));
        return this;
    }

    public StatementBuilder delete(String table) {
        sql.DELETE_FROM(table);
        return this;
    }

    public StatementBuilder consume(Consumer<SQL> consumer) {
        consumer.accept(sql);
        return this;
    }

    public SQL getSql() {
        return sql;
    }

    public void setPreSql(String preSql) {
        this.preSql = preSql;
    }

    public void setLastSql(String lastSql) {
        this.lastSql = lastSql;
    }

    @Override
    public String toString() {
        String str = sql.toString();
        if (preSql != null) {
            str = preSql + ' ' + str;
        }
        if (lastSql != null) {
            str = str + ' ' + lastSql;
        }
        if (cteSql != null) {
            str = cteSql + "\n" + str;
        }
        return str;
    }

    @Override
    public String getColByLambda(IGetter<?> getter) {
        return getColByLambda(getter, true);
    }

    private String getColByLambda(IGetter<?> getter, boolean needAlias) {
        LambdaMeta lambdaMeta = LambdaUtils.getSerializedLambda(getter);
        String fieldName = lambdaMeta.getFieldName();
        String colName = StringUtils.camelToUnderline(fieldName);
        if (!needAlias) {
            return colName;
        }
        String implClassName = lambdaMeta.getImplClassName();
        String alias;
        try {
            alias = getAndGenAlias(Class.forName(implClassName));
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        Assert.notNull(alias, "Alias cannot be empty");
        return alias + "." + colName;
    }

    private String getTableByType(Class<?> clazz) {
        TableInfo tableInfo = TableInfoHelper.getTableInfo(clazz);
        return tableInfo.getTableName();
    }

    /**
     * 获取并生成别名
     */
    private String getAndGenAlias(Class<?> clazz) {
        String alias = aliasMap.get(getTypeName(clazz));
        if (alias == null) {
            TableInfo tableInfo = TableInfoHelper.getTableInfo(clazz);
            Assert.notNull(tableInfo, "TableDef info cannot be empty");
            alias = getAbbr(tableInfo.getTableName());
            if (aliasMap.containsValue(alias)) {
                alias = alias + aliasIncr.incrementAndGet();
            }
            aliasMap.put(getTypeName(clazz), alias);
        }
        return alias;
    }

    private String getAbbr(String str) {
        StringBuilder result = new StringBuilder();
        result.append(str.charAt(0));
        Pattern pattern = Pattern.compile(ALIAS_REGEX);
        Matcher matcher = pattern.matcher(str);
        while (matcher.find()) {
            result.append(matcher.group().charAt(1));
        }
        return result.toString();
    }

    private boolean checkValue(Object value) {
        return switch (whereStrategy) {
            case ALWAYS -> true;
            case NEVER -> false;
            case NOT_EMPTY -> {
                if (value == null) yield false;
                if (value instanceof String s) {
                    yield s.trim().length() > 0;
                } else if (value instanceof Collection<?> c) {
                    yield c.size() > 0;
                } else if (value instanceof Map<?, ?> m) {
                    yield m.size() > 0;
                }
                yield true;
            }
            default -> value != null;
        };
    }

    private String formatValue(Object value) {
        if (value == null) {
            return "NULL";
        }
        String genParamName = "MPGENVAL" + this.paramNameSeq.incrementAndGet();
        String paramStr = "sw.paramNameValuePairs." + genParamName;
        this.paramNameValuePairs.put(genParamName, value);
        return SqlScriptUtils.safeParam(paramStr, null);
    }

    private String getTypeName(Object obj) {
        return ((Type) obj).getTypeName();
    }

    public class NestedConditionBuilder implements ConditionBuilder<NestedConditionBuilder> {
        private static final String OR = " \nOR ";

        private final List<String> where = new ArrayList<>();
        private List<String> lastList = new ArrayList<>();

        @Override
        public String getColByLambda(IGetter<?> getter) {
            return StatementBuilder.this.getColByLambda(getter);
        }

        private void buildWhere(StringBuilder builder, List<String> parts) {
            if (!parts.isEmpty()) {
                if (!builder.isEmpty()) {
                    builder.append("\n");
                }
                builder.append("(");
                String last = "________";
                for (int i = 0, n = parts.size(); i < n; i++) {
                    String part = parts.get(i);
                    if (i > 0 && !part.equals(OR) && !last.equals(OR)) {
                        builder.append(" AND ");
                    }
                    builder.append(part);
                    last = part;
                }
                builder.append(")");
            }
        }

        @Override
        public NestedConditionBuilder getChildren() {
            return this;
        }

        private void where(String conditions) {
            where.add(conditions);
            lastList = where;
        }

        private <T> NestedConditionBuilder where(IGetter<T> getter, Object value, String opt) {
            return where(getColByLambda(getter), value, opt);
        }

        @Override
        public NestedConditionBuilder where(String field, Object value, String opt) {
            if (checkValue(value)) {
                where(String.format("%s %s %s", field, opt, formatValue(value)));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder eq(String field, Object value) {
            return where(field, value, " = ");
        }

        @Override
        public NestedConditionBuilder ne(String field, Object value) {
            return where(field, value, " != ");
        }

        @Override
        public NestedConditionBuilder gt(String field, Object value) {
            return where(field, value, " > ");
        }

        @Override
        public NestedConditionBuilder ge(String field, Object value) {
            return where(field, value, " >= ");
        }

        @Override
        public NestedConditionBuilder lt(String field, Object value) {
            return where(field, value, " < ");
        }

        @Override
        public NestedConditionBuilder le(String field, Object value) {
            return where(field, value, " <= ");
        }

        @Override
        public NestedConditionBuilder like(String field, Object value) {
            if (checkValue(value)) {
                where(field + " LIKE CONCAT('%'," + formatValue(value) + ",'%')");
            }
            return this;
        }

        @Override
        public NestedConditionBuilder likeLeft(String field, Object value) {
            if (checkValue(value)) {
                where(field + " LIKE CONCAT('%'," + formatValue(value) + ")");
            }
            return this;
        }

        @Override
        public NestedConditionBuilder likeRight(String field, Object value) {
            if (checkValue(value)) {
                where(field + " LIKE CONCAT(" + formatValue(value) + ",'%')");
            }
            return this;
        }

        @Override
        public NestedConditionBuilder between(String field, Object val1, Object val2) {
            if (checkValue(val1) && checkValue(val2)) {
                where(String.format("%s BETWEEN %s AND %s", field, formatValue(val1), formatValue(val2)));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder in(String field, Collection<?> coll) {
            if (CollectionUtils.isNotEmpty(coll)) {
                where(String.format("%s IN (%s)", field, coll.stream().map(StatementBuilder.this::formatValue).collect(Collectors.joining(","))));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder notIn(String field, Collection<?> coll) {
            if (CollectionUtils.isNotEmpty(coll)) {
                where(String.format("%s NOT IN (%s)", field, coll.stream().map(StatementBuilder.this::formatValue).collect(Collectors.joining(","))));
            }
            return this;
        }

        @Override
        public NestedConditionBuilder isNull(String column) {
            where(column + " IS NULL");
            return this;
        }

        @SafeVarargs
        public final <T> NestedConditionBuilder isNull(boolean condition, IGetter<T>... getters) {
            if (condition) {
                return isNull(getters);
            }
            return this;
        }

        @Override
        @SafeVarargs
        public final <T> NestedConditionBuilder isNull(IGetter<T>... getters) {
            return makeGetters(getters,
                    p -> isNull(getColByLambda(p)),
                    p -> Arrays.stream(p).forEach(i -> isNull(getColByLambda(i))));
        }

        @Override
        public NestedConditionBuilder isNotNull(String column) {
            where(column + " IS NOT NULL");
            return this;
        }

        @SafeVarargs
        public final <T> NestedConditionBuilder isNotNull(boolean condition, IGetter<T>... getters) {
            if (condition) {
                return isNotNull(getters);
            }
            return this;
        }

        @Override
        @SafeVarargs
        public final <T> NestedConditionBuilder isNotNull(IGetter<T>... getters) {
            return makeGetters(getters,
                    p -> isNotNull(getColByLambda(p)),
                    p -> Arrays.stream(p).forEach(i -> isNotNull(getColByLambda(i))));
        }

        @Override
        public NestedConditionBuilder and(Consumer<ConditionBuilder<?>> consumer) {
            NestedConditionBuilder conditionSpec = new NestedConditionBuilder();
            consumer.accept(conditionSpec);
            where(conditionSpec.toString());
            return this;
        }

        @Override
        public NestedConditionBuilder or() {
            lastList.add(OR);
            return this;
        }

        @Override
        public String toString() {
            StringBuilder builder = new StringBuilder();
            buildWhere(builder, where);
            return builder.toString();
        }
    }

}
